# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import re
import sqlite3


def handle(content):
    _ = content.replace('\u3000\u3000', '\n')
    _ = _.replace('请记住本书首发域名：booktxt.net。顶点小说手机版阅读网址：m.booktxt.net', '')
    return _


class TutorialPipeline:
    def process_item(self, item, spider):
        item['id'] = int(((item['id'].split('/'))[-1].split('.'))[0])
        content = ''
        try:
            for i in item['content']:
                _ = handle(i)
                content += _
        except:
            pass
        item['content'] = content
        sql = ''' insert into {}
              (id, title, content)
              values
              ({}, '{}', '{}')'''.format(spider.table_name, item['id'], item['title'], item['content'])
        print(sql)
        spider.cursor.execute(sql)

    def close_spider(self, spider):
        spider.cursor.close()
        spider.conn.commit()
        spider.conn.close()

    def open_spider(self, spider):
        spider.conn = sqlite3.connect(spider.db_name)
        spider.cursor = spider.conn.cursor()
        sql = '''create table {} (
                id int,
                title text,
                content text)'''.format(spider.table_name)
        try:
            spider.cursor.execute(sql)
            spider.conn.commit()
        except:
            pass
